using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace OpenComposite.Base
{
	public abstract class DataAccessBase
	{
		/// <summary>
		/// Initializes a new instance of the <see cref="DataAccessBase"/> class.
		/// </summary>
		public DataAccessBase()
		{
			_conn = new SqlConnection();
		}
		/// <summary>
		/// Initializes a new instance of the <see cref="DataAccessBase"/> class.
		/// </summary>
		/// <param name="connectionstring">The connectionstring.</param>
		public DataAccessBase(string connectionstring)
		{
			_conn = new SqlConnection(connectionstring);
		}

		/// <summary>
		/// The current <see cref="System.Data.SqlClient.SqlConnection"/>.
		/// </summary>
		protected SqlConnection _conn;

		/// <summary>
		/// Creates an new SqlParameter with a value.
		/// </summary>
		/// <param name="parameterName">Name of the parameter.</param>
		/// <param name="value">The value.</param>
		/// <returns></returns>
		protected SqlParameter createSqlParam(string parameterName, object value)
		{
			return new SqlParameter(parameterName, (object)value);
		}
		/// <summary>
		/// Creates an new SqlParameter with a value and
		/// if the value equals the nullValue the SqlParameter is set to DBNull.Value.
		/// </summary>
		/// <param name="parameterName">Name of the parameter.</param>
		/// <param name="value">The value.</param>
		/// <param name="nullValue">The null value.</param>
		/// <returns></returns>
		protected SqlParameter createSqlParam(string parameterName, object value, object nullValue)
		{
			return new SqlParameter(
				parameterName,
				( value.Equals(nullValue) ? (object)DBNull.Value : (object)value ));
		}
		/// <summary>
		/// Creates a new output SqlParameter.
		/// </summary>
		/// <param name="parameterName">Name of the parameter.</param>
		/// <returns></returns>
		protected SqlParameter createOutSqlParam(string parameterName, SqlDbType dbType)
		{
			SqlParameter param = new SqlParameter(parameterName, dbType);
			param.Direction = ParameterDirection.Output;
			return param;
		}

		/// <summary>
		/// Executes a Transact-SQL statement against the current connection and
		/// fills a DataSet.
		/// </summary>
		/// <param name="sqlquery">The Transact-SQL statement.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns>The DataSet.</returns>
		protected DataSet fillDataSet(string sqlquery, params SqlParameter[] sqlparams)
		{
			return fillDataSet(null, sqlquery, sqlparams);
		}
		/// <summary>
		/// Executes a Transact-SQL statement against the current connection and
		/// fills a DataTable.
		/// </summary>
		/// <param name="sqlquery">The Transact-SQL statement.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns>The DataTable.</returns>
		protected DataTable fillDataTable(string sqlquery, params SqlParameter[] sqlparams)
		{
			return fillDataTable(null, sqlquery, sqlparams);
		}
		/// <summary>
		/// Executes a Transact-SQL statement against the current connection and
		/// returns the number of rows affected.
		/// </summary>
		/// <param name="sqlquery">The Transact-SQL statement.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns>The number of rows affected.</returns>
		protected int execNonQuery(string sqlquery, params SqlParameter[] sqlparams)
		{
			return execNonQuery(null, sqlquery, sqlparams);
		}
		/// <summary>
		/// Executes the query, and returns the first column of the first row in the result set
		/// returned by the query. Additional columns or rows are ignored.
		/// </summary>
		/// <param name="sqlquery">The Transact-SQL statement.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected object execScalar(string sqlquery, params SqlParameter[] sqlparams)
		{
			return execScalar(null, sqlquery, sqlparams);
		}
		/// <summary>
		/// Executes a Transact-SQL statement against the current connection and
		/// returns the first row of the result set. Additional rows are ignored.
		/// </summary>
		/// <param name="sqlquery">The Transact-SQL statement.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected Hashtable executeRow(string sqlquery, params SqlParameter[] sqlparams)
		{
			return executeRow(null, sqlquery, sqlparams);
		}
		/// <summary>
		/// Executes a Transact-SQL statement against the current connection and
		/// returns the rows of the result set.
		/// </summary>
		/// <param name="sqlquery">The Transact-SQL statement.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected List<Hashtable> executeRows(string sqlquery, params SqlParameter[] sqlparams)
		{
			return executeRows(null, sqlquery, sqlparams);
		}

		/// <summary>
		/// Executes the stored procedure against the current connection and
		/// returns the number of rows affected.
		/// </summary>
		/// <param name="spname">The name of the stored procedure.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected int execNonQueryProcedure(string spname, params SqlParameter[] sqlparams)
		{
			return execNonQueryProcedure(null, spname, sqlparams);
		}
		/// <summary>
		/// Executes the stored procedure, and returns the first column of the first row in the result set
		/// returned by the stored procedure. Additional columns or rows are ignored.
		/// </summary>
		/// <param name="spname">The name of the stored procedure.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected object execScalarProcedure(string spname, params SqlParameter[] sqlparams)
		{
			return execScalarProcedure(null, spname, sqlparams);
		}
		/// <summary>
		/// Executes a stored procedure against the current connection and
		/// fills a DataSet.
		/// </summary>
		/// <param name="spname">The name of the stored procedure.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected DataSet execProcedure(string spname, params SqlParameter[] sqlparams)
		{
			return execProcedure(null, spname, sqlparams);
		}

		/// <summary>
		/// Executes a Transact-SQL statement against the current connection and
		/// fills a DataSet.
		/// </summary>
		/// <param name="trans">The transaction.</param>
		/// <param name="sqlquery">The Transact-SQL statement.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected DataSet fillDataSet(SqlTransaction trans, string sqlquery, params SqlParameter[] sqlparams)
		{
			if ( _conn == null )
				throw new NullReferenceException("Connection is null.");

			DataSet ds = new DataSet();
			SqlCommand cmd = new SqlCommand(sqlquery, _conn);
			cmd.Transaction = trans;

			if ( sqlparams != null )
				cmd.Parameters.AddRange(sqlparams);

			lock ( lockThis ) {
				using ( SqlDataAdapter da = new SqlDataAdapter(cmd) ) {
					da.Fill(ds);
				}
			}
			return ds;
		}
		/// <summary>
		/// Executes a Transact-SQL statement against the current connection and
		/// fills a DataTable.
		/// </summary>
		/// <param name="trans">The transaction.</param>
		/// <param name="sqlquery">The Transact-SQL statement.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected DataTable fillDataTable(SqlTransaction trans, string sqlquery, params SqlParameter[] sqlparams)
		{
			if ( _conn == null )
				throw new NullReferenceException("Connection is null.");

			DataTable dt = new DataTable();
			SqlCommand cmd = new SqlCommand(sqlquery, _conn);
			cmd.Transaction = trans;

			if ( sqlparams != null )
				cmd.Parameters.AddRange(sqlparams);

			lock ( lockThis ) {
				using ( SqlDataAdapter da = new SqlDataAdapter(cmd) ) {
					da.Fill(dt);
				}
			}
			return dt;
		}
		/// <summary>
		/// Executes a Transact-SQL statement against the current connection and
		/// returns the number of rows affected.
		/// </summary>
		/// <param name="trans">The transaction.</param>
		/// <param name="sqlquery">The Transact-SQL statement.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected int execNonQuery(SqlTransaction trans, string sqlquery, params SqlParameter[] sqlparams)
		{
			if ( _conn == null ) throw new NullReferenceException("Connection is null.");

			int iRet = -1;
			SqlCommand cmd = new SqlCommand(sqlquery, _conn);
			cmd.Transaction = trans;
			lock ( lockThis ) {
				ConnectionState state = _conn.State;
				try {
					if ( sqlparams != null )
						cmd.Parameters.AddRange(sqlparams);

					if ( state == ConnectionState.Closed )
						_conn.Open();

					iRet = cmd.ExecuteNonQuery();
				} finally {
					if ( state == ConnectionState.Closed )
						_conn.Close();
				}
			}
			return iRet;
		}
		/// <summary>
		/// Executes the query, and returns the first column of the first row in the result set
		/// returned by the query. Additional columns or rows are ignored.
		/// </summary>
		/// <param name="trans">The transaction.</param>
		/// <param name="sqlquery">The Transact-SQL statement.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected object execScalar(SqlTransaction trans, string sqlquery, params SqlParameter[] sqlparams)
		{
			if ( _conn == null ) throw new NullReferenceException("Connection is null.");

			object objRet = null;
			SqlCommand cmd = new SqlCommand(sqlquery, _conn);
			cmd.Transaction = trans;
			lock ( lockThis ) {
				ConnectionState state = _conn.State;
				try {
					if ( sqlparams != null ) cmd.Parameters.AddRange(sqlparams);

					if ( state == ConnectionState.Closed ) _conn.Open();

					objRet = cmd.ExecuteScalar();
				} finally {
					if ( state == ConnectionState.Closed ) _conn.Close();
				}
			}
			return objRet;
		}
		/// <summary>
		/// Executes a Transact-SQL statement against the current connection and
		/// returns the first row of the result set. Additional rows are ignored.
		/// </summary>
		/// <param name="trans">The transaction.</param>
		/// <param name="sqlquery">The Transact-SQL statement.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected Hashtable executeRow(SqlTransaction trans, string sqlquery, params SqlParameter[] sqlparams)
		{
			if ( _conn == null ) {
				throw new NullReferenceException("Connection is null.");
			}
			Hashtable htRet = null;
			SqlDataReader result = null;
			SqlCommand cmd = new SqlCommand();

			lock ( lockThis ) {
				ConnectionState state = _conn.State;
				try {
					cmd.Connection = _conn;
					cmd.Transaction = trans;
					cmd.CommandText = sqlquery;

					if ( sqlparams != null && sqlparams.Length > 0 ) {
						cmd.Parameters.AddRange(sqlparams);
					}
					if ( state == ConnectionState.Closed ) {
						cmd.Connection.Open();
					}
					result = cmd.ExecuteReader();

					if ( result.Read() ) {
						htRet = new Hashtable(result.FieldCount);
						for ( int i = 0; i < result.FieldCount; i++ ) {
							object value = result.GetValue(i);
							if ( value == DBNull.Value ) value = null;
							htRet.Add(result.GetName(i), value);
						}
					}
				} finally {
					if ( result != null && !result.IsClosed ) result.Close();
					if ( state == ConnectionState.Closed ) cmd.Connection.Close();
				}
			}

			if ( htRet != null && htRet.Count > 0 ) return htRet; // EXIT

			return null;
		}
		/// <summary>
		/// Executes a Transact-SQL statement against the current connection and
		/// returns the rows of the result set.
		/// </summary>
		/// <param name="trans">The transaction.</param>
		/// <param name="sqlquery">The Transact-SQL statement.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected List<Hashtable> executeRows(SqlTransaction trans, string sqlquery, params SqlParameter[] sqlparams)
		{
			if ( string.IsNullOrEmpty(sqlquery) ) throw new ArgumentNullException("sqlquery");
			if ( _conn == null ) throw new NullReferenceException("Connection is null.");

			List<Hashtable> htRet = new List<Hashtable>();
			SqlDataReader result = null;
			SqlCommand cmd = new SqlCommand();

			cmd.Connection = _conn;
			cmd.CommandText = sqlquery;
			if ( _conn.ConnectionTimeout > cmd.CommandTimeout ) {
				cmd.CommandTimeout = _conn.ConnectionTimeout;
			}
			if ( trans != null ) cmd.Transaction = trans;
			if ( sqlparams != null && sqlparams.Length > 0 ) cmd.Parameters.AddRange(sqlparams);

			lock ( lockThis ) {
				ConnectionState state = _conn.State;
				try {
					if ( state == ConnectionState.Closed ) cmd.Connection.Open();
					result = cmd.ExecuteReader();

					while ( result.Read() ) {
						Hashtable ht = new Hashtable(result.FieldCount);
						for ( int i = 0; i < result.FieldCount; i++ ) {
							ht.Add(result.GetName(i), result.GetValue(i));
						}
						htRet.Add(ht);
					}
				} finally {
					if ( result != null && !result.IsClosed ) result.Close();
					if ( state == ConnectionState.Closed ) cmd.Connection.Close();
				}
			}
			return htRet;
		}

		/// <summary>
		/// Executes a stored procedure against the current connection and
		/// returns the number of rows affected.
		/// </summary>
		/// <param name="trans">The transaction.</param>
		/// <param name="sqlquery">The name of the stored procedure.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected int execNonQueryProcedure(SqlTransaction trans, string spname, params SqlParameter[] sqlparams)
		{
			if ( _conn == null ) throw new NullReferenceException("Connection is null.");

			int iRet = -1;
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = _conn;
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = spname;
			cmd.Transaction = trans;
			lock ( lockThis ) {
				ConnectionState state = _conn.State;
				try {
					if ( sqlparams != null && sqlparams.Length > 0 ) cmd.Parameters.AddRange(sqlparams);
					if ( state == ConnectionState.Closed ) _conn.Open();

					iRet = cmd.ExecuteNonQuery();
				} finally {
					if ( state == ConnectionState.Closed ) _conn.Close();
				}
			}
			return iRet;
		}
		/// <summary>
		/// Executes the stored procedure, and returns the first column of the first row in the result set
		/// returned by the stored procedure. Additional columns or rows are ignored.
		/// </summary>
		/// <param name="trans">The transaction.</param>
		/// <param name="sqlquery">The name of the stored procedure.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected object execScalarProcedure(SqlTransaction trans, string spname, params SqlParameter[] sqlparams)
		{
			if ( _conn == null ) throw new NullReferenceException("Connection is null.");

			object objRet = null;
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = _conn;
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = spname;
			cmd.Transaction = trans;
			lock ( lockThis ) {
				ConnectionState state = _conn.State;
				try {
					if ( sqlparams != null && sqlparams.Length > 0 ) cmd.Parameters.AddRange(sqlparams);
					if ( state == ConnectionState.Closed ) _conn.Open();

					objRet = cmd.ExecuteScalar();
				} finally {
					if ( state == ConnectionState.Closed ) _conn.Close();
				}
			}
			return objRet;
		}
		/// <summary>
		/// Executes a stored procedure against the current connection and
		/// fills a DataSet.
		/// </summary>
		/// <param name="trans">The transaction.</param>
		/// <param name="sqlquery">The name of the stored procedure.</param>
		/// <param name="sqlparams">The sql parameters.</param>
		/// <returns></returns>
		protected DataSet execProcedure(SqlTransaction trans, string spname, params SqlParameter[] sqlparams)
		{
			if ( _conn == null )
				throw new NullReferenceException("Connection is null.");

			DataSet ds = new DataSet();
			SqlCommand cmd = new SqlCommand();

			cmd.Connection = _conn;
			cmd.Transaction = trans;
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = spname;

			if ( sqlparams != null && sqlparams.Length > 0 )
				cmd.Parameters.AddRange(sqlparams);

			lock ( lockThis ) {
				using ( SqlDataAdapter da = new SqlDataAdapter(cmd) ) {
					da.Fill(ds);
				}
			}

			return ds;
		}

		/// <summary>
		/// Copies all rows in the supplied DataTable to a destination table
		/// specified by the qualifiedtablename argument.
		/// </summary>
		/// <param name="trans">The transaction.</param>
		/// <param name="qualifiedtablename">The qualified name of the destination table.</param>
		/// <param name="dtSource">The source DataTable.</param>
		protected void bulkCopy(SqlTransaction trans, string qualifiedtablename, DataTable dtSource)
		{
			if ( _conn == null ) throw new NullReferenceException("Connection is null.");

			lock ( lockThis ) {
				ConnectionState state = _conn.State;
				if ( state == ConnectionState.Closed ) _conn.Open();
				try {
					using ( SqlBulkCopy bulk = new SqlBulkCopy(_conn, SqlBulkCopyOptions.Default, trans) ) {
						bulk.DestinationTableName = qualifiedtablename;
						//bulk.BatchSize = dtSource.Rows.Count;
						bulk.WriteToServer(dtSource);
					}
				} finally {
					if ( state == ConnectionState.Closed && _conn.State != ConnectionState.Closed ) {
						_conn.Close();
					}
				}
			}
		}

		/// <summary>
		/// Begins a transaction.
		/// </summary>
		/// <returns>The newly created transaction.</returns>
		protected SqlTransaction beginTransaction()
		{
			if ( _conn.State == ConnectionState.Closed )
				_conn.Open();
			return _conn.BeginTransaction();
		}
		/// <summary>
		/// Begins a transaction.
		/// </summary>
		/// <param name="iso">The isolation level.</param>
		/// <returns>The newly created transaction.</returns>
		protected SqlTransaction beginTransaction(IsolationLevel iso)
		{
			if ( _conn.State == ConnectionState.Closed )
				_conn.Open();
			return _conn.BeginTransaction(iso);
		}
		/// <summary>
		/// Begins a transaction.
		/// </summary>
		/// <param name="transactionName">Name of the transaction.</param>
		/// <returns>The newly created transaction.</returns>
		protected SqlTransaction beginTransaction(string transactionName)
		{
			if ( _conn.State == ConnectionState.Closed )
				_conn.Open();
			return _conn.BeginTransaction(transactionName);
		}
		/// <summary>
		/// Begins a transaction.
		/// </summary>
		/// <param name="iso">The isolation level.</param>
		/// <param name="transactionName">Name of the transaction.</param>
		/// <returns>The newly created transaction.</returns>
		protected SqlTransaction beginTransaction(IsolationLevel iso, string transactionName)
		{
			if ( _conn.State == ConnectionState.Closed )
				_conn.Open();
			return _conn.BeginTransaction(iso, transactionName);
		}

		/// <summary>
		/// Commits a transaction.
		/// </summary>
		/// <param name="trans">The transaction to commit.</param>
		protected void commitTransaction(SqlTransaction trans)
		{
			trans.Commit();
			_conn.Close();
		}

		/// <summary>
		/// Rollbacks a transaction.
		/// </summary>
		/// <param name="trans">The transaction to rollback.</param>
		protected void rollbackTransaction(SqlTransaction trans)
		{
			trans.Rollback();
			_conn.Close();
		}

		/// <summary>
		/// Use the lockThis-object to lock the current instance of this class.
		/// </summary>
		protected object lockThis = new object();
		/// <summary>
		/// Use the lockAll-object to lock all instance of this class.
		/// </summary>
		protected static object lockAll = new object();
	}
}
